package com.foreveross.dao.imp;

import java.util.HashMap;
import java.util.List;

import org.apache.commons.beanutils.converters.IntegerArrayConverter;
import org.springframework.stereotype.Component;

import com.foreveross.common.Constants;
import com.foreveross.common.SQLUtil;
import com.foreveross.dao.HouseUserDAO;
import com.foreveross.vo.Express;
import com.foreveross.vo.HouseAddress;
import com.foreveross.vo.HouseRepair;
import com.foreveross.vo.HouseUser;
import com.foreveross.vo.Tenement;
import com.foreveross.vo.common.PageVo;

@Component("houseUserDAO")
public class HouseUserDAOImp extends SQLUtil implements HouseUserDAO {

	@Override
	public List<HouseUser> findHouseUserList(String name, String house_id,
			String telephone, String authentication_status, String type,
			int pageIndex, int pageSize,String userId) throws Exception {
		StringBuffer queryString = new StringBuffer(
				"select model from HouseUser model where 1=1 ");
		if (!"".equals(name) && null != name) {
			queryString.append(" and model.name like '%" + name + "%'");
		}
		if (!"".equals(house_id) && null != house_id) {
			queryString.append(" and model.hrHouses.id='" + house_id + "'");
		}
		if (!"".equals(telephone) && null != telephone) {
			queryString
					.append(" and model.telephone  like '%" + telephone + "%'");
		}

		if (!"".equals(authentication_status) && null != authentication_status) {
			queryString.append(" and model.authenticationStatus="
					+ authentication_status);
		}

		if (!"".equals(type) && null != type) {
			queryString.append(" and model.type='" + type + "'");
		}
		this.addAuthorityStr(queryString, userId, "hrHouses.id");
		queryString.append(" order by model.authenticationStatus asc,model.id desc");
		return (List<HouseUser>) findObjectAll(queryString.toString(),
				pageIndex, pageSize);
	}
 
	@Override
	public long getHouseUserListCount(String name, String house_id,
			String telephone, String authentication_status, String type,String userId)
			throws Exception {
		StringBuffer queryString = new StringBuffer(
				"select count(*) from HouseUser model where 1=1 ");
		if (!"".equals(name) && null != name) {
			queryString.append(" and model.name like '%" + name + "%'");
		}
		if (!"".equals(house_id) && null != house_id) {
			queryString.append(" and model.hrHouses.id='" + house_id + "'");
		}
		if (!"".equals(telephone) && null != telephone) {
			queryString
					.append(" and model.telephone  like '%" + telephone + "%'");
		}

		if (!"".equals(authentication_status) && null != authentication_status) {
			queryString.append(" and model.authenticationStatus="
					+ authentication_status);
		}

		if (!"".equals(type) && null != type) {
			queryString.append(" and model.type='" + type + "'");
		}
		this.addAuthorityStr(queryString, userId, "hrHouses.id");
		return (Long) findObjectAll(queryString.toString()).get(0);
	}

	@Override
	public void deleteHouseUser(String houseUserid) throws Exception {
		final String sql1 = "delete from HouseRepair t where t.houseUser.id='"
				+ houseUserid + "'";
		final String sql2 = "delete from Express t where t.houseUser.id='"
				+ houseUserid + "'";
		final String sql3 = "delete from TenementCost t where t.houseUser.id='"
				+ houseUserid + "'";
		final String sql4 = "delete from VisitorCheckIn t where t.houseUser.id='"
				+ houseUserid + "'";
		final String sql = "delete from HouseUser t where t.id='" + houseUserid
				+ "'";
		updateObejctByHql(sql1);
		updateObejctByHql(sql2);
		updateObejctByHql(sql3);
		updateObejctByHql(sql4);
		updateObejctByHql(sql);
	}

	@Override
	public long getMyHouseUserListCount(String weixinId,
			String authenticationStatus) throws Exception {
		StringBuffer queryString = new StringBuffer();
		queryString.append(" select count(*) ");
		queryString.append(" from HouseUser model where 1=1 ");
		//if (!"".equals(weixinId) && null != weixinId) {
			queryString.append(" and model.wechatId = '" + weixinId + "'");
		//}
		
		if (Constants.AUTHENTICATIONSTATUS_FAIL.equals(authenticationStatus)) {	
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_FAIL + "'");
		}else if (Constants.AUTHENTICATIONSTATUS_SUCCESS.equals(authenticationStatus)) {	
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_SUCCESS + "'");
		}else if (Constants.AUTHENTICATIONSTATUS_REJECT.equals(authenticationStatus)) {	
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_REJECT + "'");
		} else {
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_SUCCESS + "'");
		}
		queryString.append(" order by model.id desc");

		return Long.parseLong(findObjectAll(queryString.toString()).get(0).toString()) ;
	}
	
	@Override
	public long getHouseUserListCount(String weixinId,String codeId) throws Exception {
		StringBuffer queryString = new StringBuffer();
		queryString.append(" select count(*) ");
		queryString.append(" from HouseUser model where 1=1 ");
		if (!"".equals(weixinId) && null != weixinId) {
			queryString.append(" and model.wechatId = '" + weixinId + "'");
		}
		if (!"".equals(codeId) && null != codeId) {
			queryString.append(" and model.hrHouses in (select sysCode.hrHouses from SysCodeHouse sysCode where sysCode.hrCode = '" + codeId + "')");
		}
		queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_SUCCESS + "'");

		return Long.parseLong(findObjectAll(queryString.toString()).get(0).toString()) ;
	}
	
	@Override
	public List<HouseUser> getHouseUserList(String weixinId,String codeId,
			int pageIndex, int pageSize) throws Exception {
		StringBuffer queryString = new StringBuffer();
		queryString.append(" select model ");
		queryString.append(" from HouseUser model where 1=1 ");
		if (!"".equals(weixinId) && null != weixinId) {
			queryString.append(" and model.wechatId = '" + weixinId + "'");
		}
		if (!"".equals(codeId) && null != codeId) {
			queryString.append(" and model.hrHouses in (select sysCode.hrHouses from SysCodeHouse sysCode where sysCode.hrCode = '" + codeId + "')");
		}
		queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_SUCCESS + "'");
		
		queryString.append(" order by model.id desc");

		return (List<HouseUser>) findObjectAll(queryString.toString(), pageIndex,
				pageSize);
	}
	
	@Override
	public List<HouseUser> getMyHouseUserList(String weixinId,String authenticationStatus,
			int pageIndex, int pageSize) throws Exception {
		StringBuffer queryString = new StringBuffer();
		queryString.append(" select model ");
		queryString.append(" from HouseUser model where 1=1 ");
		//if (!"".equals(weixinId) && null != weixinId) {
			queryString.append(" and model.wechatId = '" + weixinId + "'");
		//}
		
		if (Constants.AUTHENTICATIONSTATUS_FAIL.equals(authenticationStatus)) {	
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_FAIL + "'");
		}else if (Constants.AUTHENTICATIONSTATUS_SUCCESS.equals(authenticationStatus)) {	
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_SUCCESS + "'");
		}else if (Constants.AUTHENTICATIONSTATUS_REJECT.equals(authenticationStatus)) {	
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_REJECT + "'");
		}else if ("3".equals(authenticationStatus)) {	//传3代表加载所有认证状态的用户数据
			queryString.append(" ");
		} else {
			queryString.append(" and authenticationStatus = '" + Constants.AUTHENTICATIONSTATUS_SUCCESS + "'");
		}
		queryString.append(" order by model.id desc");

		return (List<HouseUser>) findObjectAll(queryString.toString(), pageIndex,
				pageSize);
	}

	public List<Tenement> getUserTenement(String weixinId) throws Exception {
		StringBuffer queryString = new StringBuffer(
				"select t.* from tenement t  where t.house_id in (select u.house_id from  house_user u where u.wechat_id='"
						+ weixinId + "')");
		return findObejctBySQL(queryString.toString(), Tenement.class);
	}

	public List<HouseAddress> getHouseWYAddressList(String weixinId,
			String pageIndex, String pageSize) throws Exception {
		String mainSQL = "";
		if (pageIndex != null && !"".equals(pageIndex) && pageSize != null
				&& !"".equals(pageSize)) {
			int ipageSize = Integer.parseInt(pageSize);
			int ipageIndex = Math.max(0, (Integer.parseInt(pageIndex) - 1)
					* ipageSize);

			mainSQL = " SELECT t.* FROM ((SELECT  id as id,name as name,address as address, home as home, house_id as houseId,house_unit as houseUnit FROM house_user where wechat_id='"
					+ weixinId
					+ "') UNION ALL (SELECT -1 as id,name as name,address as address, home as home, house_id as houseId,house_unit as houseUnit FROM house_address where weixinId='"
					+ weixinId + "'))t  limit " + ipageIndex + "," + ipageSize;
		} else {
			mainSQL = " SELECT *  FROM ((SELECT  id as id,name as name,address as address, home as home, house_id as houseId,house_unit as houseUnit FROM house_user where wechat_id='"
					+ weixinId
					+ "') UNION ALL (SELECT -1 as id,name as name,address as address, home as home, house_id as houseId,house_unit as houseUnit FROM house_address where weixinId='"
					+ weixinId + "'))t";
		}

		String countSQL = "SELECT count(*) FROM ((SELECT  id as id,name as name,address as address, home as home, house_id as houseId,house_unit as houseUnit FROM house_user where wechat_id='"
				+ weixinId
				+ "') UNION ALL (SELECT -1 as id,name as name,address as address, home as home, house_id as houseId,house_unit as houseUnit FROM house_address where weixinId='"
				+ weixinId + "'))t";

		return findObejctBySQL(mainSQL, null);
	}

	@Override
	public String getWexinUserName(String weixinid) throws Exception {
		StringBuffer queryString = new StringBuffer(
				"select model from HouseUser model where 1=1 ");
		if (!"".equals(weixinid) && null != weixinid) {
			queryString.append(" and model.wechatId = '" + weixinid + "'");
		}
		List<HouseUser> list = (List<HouseUser>) findObjectAll(queryString
				.toString());
		if (list != null && !list.isEmpty())
			return list.get(0).getName();
		return null;
	}

	@Override
	public HouseUser getHouseUser(String id) throws Exception {
		return (HouseUser) this.findById(HouseUser.class, id);
	}

	@Override
	public void authentication(HouseUser houseUser) throws Exception {
		HouseUser user = (HouseUser) this.findById(HouseUser.class,
				houseUser.getId());
		user.setAuthenticationStatus(houseUser.getAuthenticationStatus());
		if (houseUser.getAuthenticationStatus() == Integer.parseInt(Constants.AUTHENTICATIONSTATUS_SUCCESS)) {
			user.setAddress(houseUser.getAddress());
			user.setCttId(houseUser.getCttId());
			user.setCusId(houseUser.getCusId());
			user.setResId(houseUser.getResId());
			user.setResCode(houseUser.getResCode());
			user.setIDCardName(houseUser.getIDCardName());
			user.setIDCardNo(houseUser.getIDCardNo());
		}
		this.updateObject(user);
	}
 

	public String getTenement(String userid) throws Exception {
		String sql = "select t.id from tenement t, tenement_house th,house_user u where u.id='"
				+ userid
				+ "' and t.id=th.tenement_id and th.house_id=u.house_id";
		List list = this.findObejctBySQL(sql, null);
		if (list != null && !list.isEmpty()) {
			Object[] obj = (Object[]) list.get(0);
			return obj[0].toString();
		}
		return null;
	}

	@Override
	public List<HouseUser> getHouseUserByweixinId(String weixinId)
			throws Exception {
		String sql = "select model from HouseUser model where 1=1 and wechatId='"
				+ weixinId + "' and authenticationStatus='"+Constants.AUTHENTICATIONSTATUS_SUCCESS+"'";
		return (List<HouseUser>) findObjectAll(sql);
	}

	@Override
	public List<HouseUser> getHouseUserByCusIdAndResId(String cusId,String resId) throws Exception {
		String sql = "select model from HouseUser model where 1=1 and resId='"
				+ resId + "' and cusId='"+cusId+"' and authenticationStatus='"+Constants.AUTHENTICATIONSTATUS_SUCCESS+"'";
		return (List<HouseUser>) findObjectAll(sql);
	}

}
